from sqlalchemy.orm import Session
from sqlalchemy import desc, asc, or_,func
from typing import List, Optional, Dict, Any
from datetime import datetime
from educate_ai.questions_part import model
from educate_ai.questions_part import schema


# 创建问题
def create_question(db: Session, question: schema.QuestionCreate) -> model.Question:
    """
    创建新问题
    """
    db_question = model.Question(
        content=question.content,
        answer=question.answer,
        question_type=question.question_type,
        difficulty=question.difficulty,
        major=question.major,
        course=question.course,
        subject=question.subject,
        analysis=question.analysis,
        question_time=question.question_time
    )
    db.add(db_question)
    db.commit()
    db.refresh(db_question)
    return db_question


# 批量创建问题
def create_questions_bulk(db: Session, questions: List[schema.QuestionCreate]) -> List[model.Question]:
    """
    批量创建问题
    """
    db_questions = []
    for question_data in questions:
        db_question = model.Question(
            content=question_data.content,
            answer=question_data.answer,
            question_type=question_data.question_type,
            difficulty=question_data.difficulty,
            major=question_data.major,
            course=question_data.course,
            subject=question_data.subject,
            analysis=question_data.analysis,
            question_time=question_data.question_time
        )
        db_questions.append(db_question)
        db.add(db_question)

    db.commit()

    # 刷新所有对象以获取数据库生成的ID
    for question in db_questions:
        db.refresh(question)

    return db_questions


# 获取问题
def get_question(db: Session, question_id: int) -> Optional[model.Question]:
    """
    根据ID获取单个问题
    """
    return db.query(model.Question).filter(model.Question.id == question_id).first()


# 获取问题列表
def get_questions(
        db: Session,
        skip: int = 0,
        limit: int = 100,
        course: Optional[str] = None,
        major: Optional[str] = None,
        subject: Optional[str] = None,
        question_type: Optional[str] = None,
        difficulty: Optional[int] = None,
        difficulty_min: Optional[int] = None,
        difficulty_max: Optional[int] = None,
        search: Optional[str] = None,
        sort_by: str = "created_at",
        sort_order: str = "desc"
) -> List[model.Question]:
    """
    获取问题列表，支持过滤和排序
    """
    query = db.query(model.Question)

    # 应用过滤器
    if course:
        query = query.filter(model.Question.course == course)
    if major:
        query = query.filter(model.Question.major == major)
    if subject:
        query = query.filter(model.Question.subject == subject)
    if question_type:
        query = query.filter(model.Question.question_type == question_type)
    if difficulty:
        query = query.filter(model.Question.difficulty == difficulty)
    if difficulty_min is not None:
        query = query.filter(model.Question.difficulty >= difficulty_min)
    if difficulty_max is not None:
        query = query.filter(model.Question.difficulty <= difficulty_max)

    # 搜索功能
    if search:
        query = query.filter(
            or_(
                model.Question.content.ilike(f"%{search}%"),
                model.Question.answer.ilike(f"%{search}%"),
                model.Question.analysis.ilike(f"%{search}%")
            )
        )

    # 应用排序
    if hasattr(model.Question, sort_by):
        if sort_order.lower() == "desc":
            query = query.order_by(desc(getattr(model.Question, sort_by)))
        else:
            query = query.order_by(asc(getattr(model.Question, sort_by)))
    else:
        # 默认排序
        query = query.order_by(desc(model.Question.created_at))

    return query.offset(skip).limit(limit).all()


# 获取问题总数（用于分页）
def get_questions_count(
        db: Session,
        course: Optional[str] = None,
        major: Optional[str] = None,
        subject: Optional[str] = None,
        question_type: Optional[str] = None,
        difficulty: Optional[int] = None,
        difficulty_min: Optional[int] = None,
        difficulty_max: Optional[int] = None,
        search: Optional[str] = None
) -> int:
    """
    获取满足条件的问题总数
    """
    query = db.query(model.Question)

    # 应用过滤器（与get_questions保持一致）
    if course:
        query = query.filter(model.Question.course == course)
    if major:
        query = query.filter(model.Question.major == major)
    if subject:
        query = query.filter(model.Question.subject == subject)
    if question_type:
        query = query.filter(model.Question.question_type == question_type)
    if difficulty:
        query = query.filter(model.Question.difficulty == difficulty)
    if difficulty_min is not None:
        query = query.filter(model.Question.difficulty >= difficulty_min)
    if difficulty_max is not None:
        query = query.filter(model.Question.difficulty <= difficulty_max)
    if search:
        query = query.filter(
            or_(
                model.Question.content.ilike(f"%{search}%"),
                model.Question.answer.ilike(f"%{search}%"),
                model.Question.analysis.ilike(f"%{search}%")
            )
        )
    return query.count()


# 更新问题
def update_question(
        db: Session,
        question_id: int,
        question_update: schema.QuestionUpdate
) -> Optional[model.Question]:
    """
    更新问题信息
    """
    db_question = get_question(db, question_id)
    if not db_question:
        return None

    update_data = question_update.model_dump(exclude_unset=True)

    for field, value in update_data.items():
        setattr(db_question, field, value)

    db_question.updated_at = datetime.now()

    db.commit()
    db.refresh(db_question)
    return db_question


# 部分更新问题
def partial_update_question(
        db: Session,
        question_id: int,
        update_data: Dict[str, Any]
) -> Optional[model.Question]:
    """
    部分更新问题字段
    """
    db_question = get_question(db, question_id)
    if not db_question:
        return None

    for field, value in update_data.items():
        if hasattr(db_question, field):
            setattr(db_question, field, value)

    db_question.updated_at = datetime.now()

    db.commit()
    db.refresh(db_question)
    return db_question


# 删除问题
def delete_question(db: Session, question_id: int) -> bool:
    """
    删除问题
    """
    db_question = get_question(db, question_id)
    if not db_question:
        return False

    db.delete(db_question)
    db.commit()
    return True


# 批量删除问题
def delete_questions_bulk(db: Session, question_ids: List[int]) -> int:
    """
    批量删除问题，返回删除的数量
    """
    result = db.query(model.Question).filter(model.Question.id.in_(question_ids)).delete()
    db.commit()
    return result


# 检查问题是否存在
def question_exists(db: Session, question_id: int) -> bool:
    """
    检查问题是否存在
    """
    return db.query(db.query(model.Question).filter(model.Question.id == question_id).exists()).scalar()


# 获取课程列表
def get_courses(db: Session) -> List[str]:
    """
    获取所有课程的列表
    """
    return [result[0] for result in db.query(model.Question.course).distinct().all()]


# 获取专业列表
def get_majors(db: Session) -> List[str]:
    """
    获取所有专业的列表
    """
    return [result[0] for result in db.query(model.Question.major).distinct().all() if result[0]]


# 获取科目列表
def get_subjects(db: Session) -> List[str]:
    """
    获取所有科目的列表
    """
    results = db.query(model.Question.subject) \
        .filter(model.Question.subject.isnot(None)) \
        .distinct() \
        .all()

    return [result[0] for result in results if result[0]]


# 根据条件统计问题数量
def get_question_stats(db: Session) -> Dict[str, Any]:
    """
    获取问题统计信息
    """
    stats = {}

    # 按类型统计
    type_stats = db.query(
        model.Question.question_type,
        func.count(model.Question.id)
    ).group_by(model.Question.question_type).all()
    tuple_list = [(row[0], row[1]) for row in type_stats]
    stats['by_type'] = dict(tuple_list)

    # 按难度统计
    difficulty_stats = db.query(
        model.Question.difficulty,
        func.count(model.Question.id)
    ).group_by(model.Question.difficulty).all()
    tuple_list = [(row[0], row[1]) for row in difficulty_stats]
    stats['by_difficulty'] = dict(tuple_list)

    # 按课程统计
    course_stats = db.query(
        model.Question.course,
        func.count(model.Question.id)
    ).group_by(model.Question.course).all()
    tuple_list = [(row[0], row[1]) for row in course_stats]
    stats['by_course'] = dict(tuple_list)

    return stats